package com.easyj.base.common.export;

import java.math.BigDecimal;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFDrawing;
import org.apache.poi.xssf.streaming.SXSSFPicture;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.easyj.base.util.DateTimeUtil;


/**
 * 通用导出excel...
 * @author hjf
 *
 * @param <T>
 */

public abstract class ExpExcutor<T> {
     
	public Logger log =  LoggerFactory.getLogger(getClass());
	 protected SXSSFWorkbook workbook;
     protected CellStyle columnHeadStyle;
     protected CellStyle columnHeadStyleRed;
     protected CellStyle downStyle;
     protected CellStyle desctstyle;
     protected boolean descFlag=false;
     protected boolean needPage=true;
     protected boolean idColFlag=false;
     protected String desc;
     protected int sheetSize=60000;//max page
     protected List<ExpField> fields;
     //---------
     protected  int startRow=1;
	   
     
     protected HashMap<String,CellStyle> fieldsStyleMap=new HashMap<String,CellStyle>();
     
     protected  void createWorkBook() throws Exception{
		workbook = new SXSSFWorkbook(300);// 创建一个Excel流文件  
		initWorkBook();
	}
     
     
    protected SXSSFWorkbook getWorkbook(){
    	return workbook;
    }
     
     protected void initWorkBook(){

 		Font descfont = workbook.createFont(); 
 		descfont.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
 		desctstyle = workbook.createCellStyle(); 
 		desctstyle.setFont(descfont);
 		desctstyle.setWrapText(true);
 		
 		
 	    
 	   // 设置表头字体  red
 	    Font headfontRed = workbook.createFont(); 
 	    headfontRed.setFontName("黑体");  
	    headfontRed.setFontHeightInPoints((short) 12);// 字体大小  
	    headfontRed.setBold(true);// 加粗  
	    headfontRed.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
	    
 	    // 设置表头字体  black
 	    Font headfont = workbook.createFont(); 
 	    headfont.setFontName("黑体");  
 	    headfont.setFontHeightInPoints((short) 12);// 字体大小  
 	    headfont.setBold(true);// 加粗  
 	    
 	// 表头的样式 ... 
 	    columnHeadStyleRed = workbook.createCellStyle();  
 	    columnHeadStyleRed.setFont(headfontRed);  
 	    columnHeadStyleRed.setLocked(true);
 	   columnHeadStyleRed.setVerticalAlignment(VerticalAlignment.CENTER);
 	   columnHeadStyleRed.setAlignment(HorizontalAlignment.CENTER);
 	   columnHeadStyleRed.setBorderBottom(BorderStyle.THIN);
 	   columnHeadStyleRed.setBottomBorderColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
 	   columnHeadStyleRed.setBorderLeft(BorderStyle.THIN);
 	   columnHeadStyleRed.setLeftBorderColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
 	   columnHeadStyleRed.setBorderRight(BorderStyle.THIN);
 	   columnHeadStyleRed.setRightBorderColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
 	    columnHeadStyleRed.setBorderTop(BorderStyle.THIN);
 	     columnHeadStyleRed.setTopBorderColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());	   
	    //设置单元格的背景颜色		    
 	    columnHeadStyleRed.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
	    columnHeadStyleRed.setFillPattern(FillPatternType.SOLID_FOREGROUND);
 	    
	 // 表头的样式 ... 
 	    columnHeadStyle = workbook.createCellStyle();  
 	    columnHeadStyle.setFont(headfont);  
 	    columnHeadStyle.setLocked(true);
 	    columnHeadStyle.setVerticalAlignment(VerticalAlignment.CENTER);
 	    columnHeadStyle.setAlignment(HorizontalAlignment.CENTER);
 	    columnHeadStyle.setBorderBottom(BorderStyle.THIN);
 	    columnHeadStyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
 	    columnHeadStyle.setBorderLeft(BorderStyle.THIN);
 	    columnHeadStyle.setLeftBorderColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
 	    columnHeadStyle.setBorderRight(BorderStyle.THIN);
 	    columnHeadStyle.setRightBorderColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
 	    columnHeadStyle.setBorderTop(BorderStyle.THIN);
 	    columnHeadStyle.setTopBorderColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex()); 	   
 	    //设置单元格的背景颜色	
 	    columnHeadStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
 	    columnHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
 	   
 	    downStyle = workbook.createCellStyle();
 	    downStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);
 	    downStyle.setAlignment(HorizontalAlignment.CENTER);
 	    //数据格式....
 	    for(int i=0;i<fields.size();i++){
	    	ExpField field=fields.get(i);
	    	CellStyle fieldstyle = workbook.createCellStyle();  
	   	   
	   	    fieldstyle.setBorderBottom(BorderStyle.THIN);
	   	    fieldstyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
	   	    fieldstyle.setBorderLeft(BorderStyle.THIN);
	   	    fieldstyle.setLeftBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
	   	    fieldstyle.setBorderRight(BorderStyle.THIN);
	   	    fieldstyle.setRightBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
	   	    fieldstyle.setBorderTop(BorderStyle.THIN);
	   	    fieldstyle.setTopBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
	   	    
	   	    fieldstyle.setVerticalAlignment(VerticalAlignment.CENTER);
	   	    
	   	    if(field.getAlign()!=null && field.getAlign().equals("center")){
	   		 fieldstyle.setAlignment(HorizontalAlignment.CENTER);
	        }else    if(field.getAlign()!=null && field.getAlign().equals("right")){
	    	  fieldstyle.setAlignment(HorizontalAlignment.RIGHT); 
	        }else { //default
	    	  fieldstyle.setAlignment(HorizontalAlignment.LEFT);
	       }
	  	 	   	   
	   	    fieldsStyleMap.put(field.getName(), fieldstyle);
	     }
 	    
     }
     protected void createFieldsTitle(SXSSFSheet sheet,List<ExpField> fields){
    	 startRow=1;//default
    	 if(descFlag){   		   		 
    		// 创建第一行  说明
	 	     SXSSFRow row0 = sheet.createRow(0);  
	 	     SXSSFCell cell0 = row0.createCell(0);
	 	     sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));	 	    
	 	     cell0.setCellStyle(desctstyle);
	 	     if(StringUtils.isBlank(desc)) {
		 	     StringBuffer buf=new StringBuffer();
		 	     buf.append("说明：\n");
		 	     if(this.idColFlag) {
		 	       buf.append("1.第1列（ID）为每一行的数据的标识。\n");
		 	       buf.append("2.若通过先导出，批量修改数据后，再导入，请不要修改ID列。\n");
		 	     }
		 	     
		 	     cell0.setCellValue(buf.toString());
		 	      // 设置行高  
		 	      row0.setHeight((short) 1500);
	 	     }else {
	 	    	cell0.setCellValue(desc);
	 	    	String[] descs=desc.split("\n");
	 	    	row0.setHeight((short)(400+descs.length*300)); 
	 	     }
	 	     
	 	    //------
    		 startRow=2;
	 	      
    	 } 
 	      //表头
 	      SXSSFRow row1 = sheet.createRow(startRow-1); 
 	      row1.setHeight((short) 450);
 	      for(int i=0;i<fields.size();i++){
 	    	  ExpField field=fields.get(i);
 	    	  SXSSFCell c = row1.createCell(i);
 	    	  if(field.isRequired()) {
 	    		  c.setCellStyle(columnHeadStyleRed);
 	    	  }else {
 	    	     c.setCellStyle(columnHeadStyle);
 	    	  }
 	    	  c.setCellValue(field.getTitle());
 	    	  
 	      }
     }
     
     protected  void createSheet(List<ExpField> fields,SXSSFWorkbook workbook,List<T> datas) throws Exception{
 		SXSSFSheet sheet = workbook.createSheet();
 		short rowHeight=400;//(1/20 of  a point)
 		for(int i=0;i<fields.size();i++){
 			ExpField field=fields.get(i);
 			if("barcode".equals(field.getFormat())) {
 				rowHeight=800;
 			}
 	        sheet.setColumnWidth(i, field.getWidth()*256);
 		}
 		 sheet.setDefaultRowHeight(rowHeight);
 	     this.createFieldsTitle(sheet, fields); 	     
 	   
 	      //数据..
 	    if(datas!=null){
 		      for(int n=0;n<datas.size();n++){
 		    	  T vo=datas.get(n);
 		    	  SXSSFRow r = sheet.createRow(n+startRow);
 		    	  r.setHeight(rowHeight);//(1/20 of  a point)
 		    	  for(int i=0;i<fields.size();i++){
 			    	  ExpField field=fields.get(i);
 			    	  SXSSFCell c = r.createCell(i);
 			    	  
 				      	     
 			    	  Object value=null;
 			    	  if(field.getName().indexOf(".")>-1){
 			    		 // value = PropertyUtils.getNestedProperty(vo, field.getField());//不直接使用getNestedProperty；多层属性，中间如果有null,会报错。
 			    		  String[] s=field.getName().split("\\.");
 			    		  Object tobj=vo;
 			    		  for(int z=0;z<s.length;z++){
 			    			 try{ 
 			    			    tobj= PropertyUtils.getProperty(tobj, s[z]);
 			    			  }catch(Exception el){
 			    				 tobj=null;
 	 			    		  }
 			    			 if(tobj==null){
 			    				 break;
 			    			 }
 			    		  }
 			    		  value=tobj;
 			    	  }else{
 			    		  try{
 			    			 value = PropertyUtils.getProperty(vo, field.getName());
 			    		  }catch(Exception el){
 			    			  
 			    		  }
 					      
 			    	  }
 			    	  
 					  //--
 					  setCellValue(sheet,n,c,value,field,vo);
 					  
 			    	 	  
 		    	  }    
 		      }
 	      }
 	}
     
     protected void setCellValue(SXSSFSheet sheet ,int rowNum,SXSSFCell c ,Object value,ExpField field, T bean){
    	//cellstyle
	      c.setCellStyle(fieldsStyleMap.get(field.getName())); 
	    
    	 if(value!=null){
    	     if( value instanceof String){
    	    	 String v=(String)value;
    	    	 if(StringUtils.isNotBlank(field.getFormat())) {    	    		    
					 c.setCellType(CellType.STRING);
					 c.setCellValue(v);
    	    	 }else { 		  
				   c.setCellType(CellType.STRING);
				   c.setCellValue(v);
    	    	 }
    	      }else if( value instanceof Boolean){	//booleanFormat--- 
    	    	  Boolean bl=(Boolean)value;
    	    	  c.setCellType(CellType.STRING);
    	    	  c.setCellValue(bl?"是":"否");
    	      }else if( value instanceof Date){	
    	    	  c.setCellType(CellType.STRING); //常用格式
    	    	  if(StringUtils.isNotBlank(field.getFormat())) {
    	    		  if("date".equals(field.getFormat())) {
    	    			  c.setCellValue(DateTimeUtil.getDateStr((Date)value));
    	    		  }else {
    	    			  c.setCellValue(DateTimeUtil.getDateTimeStr((Date)value));
    	    		  }
    	    	  }else {
    	    	     //default yyyy-MM-dd HH:mm:ss
 			          c.setCellValue(DateTimeUtil.getDateTimeStr((Date)value));
    	    	  }
    	      }else if( value instanceof BigDecimal){
    	    	  c.setCellType(CellType.STRING);
    	    	  BigDecimal b=(BigDecimal)value;
				  c.setCellValue(b.toString());
    	      }else if( value instanceof Short){	 
    	    	  Short  v=(Short)value;
				  if(StringUtils.isNotBlank(field.getFormat())) {
					  if("sexFormat".equals(field.getFormat())) {
						  c.setCellType(CellType.STRING);
						  if(v==1) {
							  c.setCellValue("男"); 
						  }else if(v==2) {
							  c.setCellValue("女"); 
						  } else {
							  c.setCellValue("未知"); 
						  }
					  }else if("statusFormat".equals(field.getFormat())) {
						  c.setCellType(CellType.STRING);
						  if(v==1) {
							  c.setCellValue("正常"); 
						  }else if(v==0) {
							  c.setCellValue("停用"); 
						  } else {
							  c.setCellValue("非法值："+v); 
						  }
					  }else {
						  c.setCellType(CellType.STRING);
	    				  c.setCellValue(v.toString());
					  }
				  }else {
    				  c.setCellType(CellType.STRING);
    				  c.setCellValue(v.toString());
				  }	  
    	      }else if( value instanceof Integer){	 
    	    	      Integer  v=(Integer)value;
    	    	      if(StringUtils.isNotBlank(field.getFormat())) {
    					  if("sexFormat".equals(field.getFormat())) {
    						  c.setCellType(CellType.STRING);
    						  if(v==1) {
    							  c.setCellValue("男"); 
    						  }else if(v==2) {
    							  c.setCellValue("女"); 
    						  } else {
    							  c.setCellValue("未知"); 
    						  }
    					  }else if("statusFormat".equals(field.getFormat())) {
    						  c.setCellType(CellType.STRING);
    						  if(v==1) {
    							  c.setCellValue("正常"); 
    						  }else if(v==0) {
    							  c.setCellValue("停用"); 
    						  } else {
    							  c.setCellValue("非法值："+v); 
    						  }	  
    					  }else {
    						  c.setCellType(CellType.STRING);
    	    				  c.setCellValue(v.toString());
    					  }
    				  }else {
        				  c.setCellType(CellType.STRING);
        				  c.setCellValue(v.toString());
    				  }	 
			  }else if( value instanceof Number){//double
				  c.setCellType(CellType.STRING);				  
				  c.setCellValue(value.toString());
			  }else {
				 c.setCellType(CellType.STRING);
			     c.setCellValue(value.toString());
			  }
    	 }   
     }
    
     /**
      * 取行总记录条数
      * @return
      */
     protected  abstract Long getRecordCount();
    /**
     * 取每个sheet的数据
     * @param sheetSize
     * @param sheetinx
     * @return
     */
     protected  abstract List<T> getSheetData(final int sheetSize,int sheetinx);
     
     /**
      * @param idColFlag 需要id列
      * @param descFlag  需要描述
      * @param needPage  需要分页
      */
     public  SXSSFWorkbook export(List<ExpField> fields,boolean idColFlag,boolean descFlag,String desc,boolean needPage,int sheetSize){
    	 this.descFlag=descFlag;
    	 this.idColFlag=idColFlag;  
    	 this.sheetSize=sheetSize;
    	 this.needPage=needPage;
    	 this.desc=desc;
    	 return export(fields);
     }
     
     public  SXSSFWorkbook export(List<ExpField> fields,boolean idColFlag,boolean descFlag,String desc){
    	 this.descFlag=descFlag;
    	 this.idColFlag=idColFlag;  
    	 this.desc=desc;
    	 return export(fields);
     }
     
     /**
      * 
      * @param iddescFlag 
      * iddescFlag=true 导出Id和数据，第一行是要描述，一般用于导入修改数据
      */
     public  SXSSFWorkbook export(List<ExpField> fields,boolean idColFlag,boolean iddescFlag,boolean needPage){
    	 this.needPage=needPage;
    	 this.descFlag=iddescFlag;
    	 this.idColFlag=idColFlag;
    	 return export(fields);
     }
     
     
     
     /**
      * 
      * @param iddescFlag 
      * iddescFlag=true 导出Id和数据，第一行是要描
      */
     public  SXSSFWorkbook export(List<ExpField> fields,boolean iddescFlag){
    	 this.idColFlag=iddescFlag;  //不需要导入描述，也不需id 列特别处理   	 
    	 return export(fields);
     }
    
    /**
     * 导出EXCEL
     * @param fields
     * @return
     * @throws Exception
     */
    public  SXSSFWorkbook export(List<ExpField> fields){
	    try{	

    	    //ID 列----
    	    if(idColFlag){
	    	    for(ExpField e:fields){
	    	    	if(e.getName().equals("id")){
	    	    		fields.remove(e);
	    	    		break;
	    	    	}
	    	    }
	    	    ExpField ide=new ExpField();
	    	    ide.setName("id");
	    	    ide.setAlign("left");
	    	    ide.setTitle("ID");
	    	    fields.add(0,ide);
    	    }
    	    
    	    //init book---
    	    this.fields=fields;
    	    createWorkBook();
	    	 
    	    //read data
	    	if(needPage){
	    		Long total=getRecordCount();
	 			if(total<=0l){
	 				 SXSSFSheet sheet = workbook.createSheet();
	 				 for(int i=1;i<fields.size();i++){
	 			 	     sheet.setColumnWidth(i, 16*256);
	 			 	  }
	 		 	     sheet.setDefaultRowHeight((short)400);//1/20 of  a point
	 		 	     this.createFieldsTitle(sheet, fields);
	 		 	     return workbook;
	 			}
	 			
	    		//分页sheet 导出
				Long totalPage = total/sheetSize;
				if(total%sheetSize>0){
					totalPage=totalPage+1;
				}
				// sheet
				 for(int sheetinx=0;sheetinx<totalPage;sheetinx++){
						List<T> datas= this.getSheetData(sheetSize, sheetinx);
						if(datas!=null&&datas.size()>0){
							this.createSheet(fields, workbook, datas);
						}
				 }	
	    	}else {//不分页
	    		
	    		List<T> datas= this.getSheetData(sheetSize, 0);
				if(datas!=null&&datas.size()>0){
					this.createSheet(fields, workbook, datas);
				}else {
					 SXSSFSheet sheet = workbook.createSheet();
	 				 for(int i=1;i<fields.size();i++){
	 			 	     sheet.setColumnWidth(i, 16*256);
	 			 	  }
	 		 	     sheet.setDefaultRowHeight((short)400);//1/20 of  a point
	 		 	     this.createFieldsTitle(sheet, fields);
				}
	    	}
	    }catch(Exception ex){
	    	log.error("导出Excel错误！", ex);
	    	try{
		    	if(workbook!=null){
		    		 SXSSFSheet sheet2=null;
		    		 
		    		 sheet2 = workbook.createSheet("导出Excel错误！");
			 	     sheet2.setDefaultColumnWidth(30);
			 	     SXSSFRow row0 = sheet2.createRow(0);  
			 	     SXSSFCell cell0 = row0.createCell(0);
			 	     sheet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));
			 	     cell0.setCellValue("导出Excel错误！"+ex.getMessage());
			 	      
			 	     int inx=workbook.getSheetIndex(sheet2);
			 	     workbook.setActiveSheet(inx);
		    	}
		    	
	    	}catch(Exception e){
	    		
	    	}
	    }
    	return workbook;
    }
    
    protected void pictureToSheet(SXSSFSheet finalSheet, SXSSFCell cell, int pictureIdx) {
    	SXSSFDrawing patriarch = finalSheet.createDrawingPatriarch();
       // ExcelPositionRange excelPositionRange = ExcelTransferUtils.getMergedRegionPositionRange(finalSheet, row.getRowNum(), cell.getColumnIndex());
    	ClientAnchor anchor = patriarch.createAnchor(50, 50, 1,1,
                cell.getColumnIndex(),
                cell.getRowIndex(),
                cell.getColumnIndex()+1,
                cell.getRowIndex()+1
        );
    	
    	anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
    	SXSSFPicture pic=patriarch.createPicture(anchor, pictureIdx);
    	//pic.setNoFill(false);
        pic.resize(1,0.6);
    }
}
